package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Category;
import cn.com.dashihui.web.dao.DeatiImage;
import cn.com.dashihui.web.dao.GoodsBaseImages;
import cn.com.dashihui.web.dao.GoodsSale;
import cn.com.dashihui.web.dao.GoodsSaleItem;
import cn.com.dashihui.web.dao.GoodsStore;
import cn.com.dashihui.web.dao.GoodsTokerValue;
import cn.com.dashihui.web.dao.User;

public class GoodsStoreService {
	public boolean update(GoodsStore object){
		return object.update();
	}
	public GoodsStore findGoodById(int id) {
		return GoodsStore.me().findFirst("select * from t_bus_goods where id=?",id);
    }
	/**
	 * 分页查找分公司产品
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	public Page<Record> findByPage(int pageNum, int pageSize,String index,int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select L.goodsid ,count(L.id) AS countSell from t_bus_order_list L inner join t_bus_order O on O.orderNum = L.orderNum where O.orderState NOT in(1,5,7,9) group by L.goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J inner join t_bus_order K on J.orderNum = K.orderNum  where K.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
	   if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
	}
			sBuffer.append(" AND A.groupbuying = 1 ");
			sBuffer.append(" GROUP BY A.id ORDER BY A.id DESC");

		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState  ", sBuffer.toString(), params.toArray());
	}
	//查询订单
		public Page<Record> goodsOrder(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,int seid, String tel, int stid, int id) {
			StringBuffer sBuffer = new StringBuffer();
			List<Object> params = new ArrayList<Object>();
			sBuffer.append(" FROM t_bus_order_list LI "
					+ "LEFT JOIN t_bus_order A ON A.orderNum = LI.orderNum "
					+ "LEFT JOIN t_bus_user F on A.userid=F.id "
					+ "LEFT JOIN t_bus_goods D ON D.id = LI.goodsid "
					+ "LEFT JOIN t_bus_goods_store_rel G ON G.goodstore = D.good_store "
					+ "LEFT JOIN t_dict_store B ON G.storeid = B.storeid "
					+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
					+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
					+ " where 1=1 ");
			params.add(id);
			sBuffer.append(" AND LI.goodsid=?");
			if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
				params.add(beginDate);
				params.add(endDate);
			}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
				params.add(beginDate);
			}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
				params.add(endDate);
			}
			
			sBuffer.append(" AND A.orderState!=1 ");
			sBuffer.append(" AND A.orderState!=5 ");
			sBuffer.append(" AND A.orderState!=7 ");
			sBuffer.append(" AND A.orderState!=9 ");
			if(!StrKit.isBlank(tel)){
				sBuffer.append(" AND A.tel=?");
				params.add(tel);
			}
			sBuffer.append(" ORDER BY A.startDate DESC");
			//查询出符合条件的订单列表
			Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid",sBuffer.toString(),params.toArray());
			return page;
		}
		/**
		 * 查询团购订单
		 * @param pageNum
		 * @param pageSize
		 * @param orderNum
		 * @param beginDate
		 * @param endDate
		 * @param state
		 * @param seid
		 * @param tel
		 * @param stid
		 * @param id
		 * @return
		 */
		//查询订单
		public Page<Record> goodsOrdergb(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,int seid, String tel, int stid, int id) {
			StringBuffer sBuffer = new StringBuffer();
			List<Object> params = new ArrayList<Object>();
			sBuffer.append(" FROM t_bus_order_gb A "
					+ "inner JOIN t_bus_user F on A.userid=F.id "
					+ "LEFT JOIN t_bus_goods D ON D.id = A.goodsid "
					+ "LEFT JOIN t_bus_goods_store_rel G ON G.goodstore = D.good_store "
					+ "LEFT JOIN t_dict_store B ON G.storeid = B.storeid "
					+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId  "
					+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
					+ " where 1=1 ");
			params.add(id);
			sBuffer.append(" AND A.goodsid=?");
			
			if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
				params.add(beginDate);
				params.add(endDate);
			}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
				params.add(beginDate);
			}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
				params.add(endDate);
			}
			sBuffer.append(" AND A.orderState!=1 ");
			sBuffer.append(" AND A.orderState!=5 ");
			sBuffer.append(" AND A.orderState!=7 ");
			sBuffer.append(" AND A.orderState!=9 ");
			if(!StrKit.isBlank(tel)){
				sBuffer.append(" AND A.tel=?");
				params.add(tel);
			}
			sBuffer.append(" ORDER BY A.startDate DESC");
			//查询出符合条件的订单列表
			Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid",sBuffer.toString(),params.toArray());
			return page;
		}
	/**
	 * 分页查找团购产品
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	public Page<Record> findByPagegb(int pageNum, int pageSize,String index,int goodId,String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId " 
				+ "LEFT JOIN (select L.goodsid ,sum(L.countnum) AS countSell from t_bus_order_gb L where L.orderState NOT in(1,5,7,9) group by L.goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.amount) AS countPrice from t_bus_order_gb J  where J.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
	   if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
			sBuffer.append(" AND A.groupbuying = 2 ");
			sBuffer.append(" GROUP BY A.id ORDER BY A.id DESC");

		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title as xdstore,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 分页查找分公司产品销量排序
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	public Page<Record> findByPageSort(int pageNum, int pageSize,String index,int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId "
				+ "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id"
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
        //订单量、收藏量、浏览量排序功能，可按大到小排 从小到大
		if(!StrKit.isBlank(index)){
			switch (index) {
				case "1":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell DESC");
					break;
				case "2":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell ASC");
					break;						
				case "3":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon DESC");
					break;
				case "4":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon ASC");
					break;		
				case "5":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum DESC");
					break;
				case "6":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum ASC");
					break;	
				case "7":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice DESC");
					break;	
				case "8":
					sBuffer.append(" GROUP BY A.id ORDER BY I.countPrice ASC");
					break;	
			}	
		}
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 分页查找分公司产品团购销量排序
	 * @param pageNum
	 * @param pageSize
	 * @param storeid 分公司ID
	 * @param s 商品在售状态，1：在售，2：已下架，3：待审核
	 * @param c1,c2,c3,c4 四级分类
	 * @param keyword 搜索关键字
	 */
	public Page<Record> findByPageSortgb(int pageNum, int pageSize,String index,int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId "
				+ "LEFT JOIN (select goodsid ,sum(countnum) AS countSell,sum(amount) AS countPrice from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id "
				+ "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id"
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
	   if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 2 ");
        //订单量、收藏量、浏览量排序功能，可按大到小排 从小到大
		if(!StrKit.isBlank(index)){
			switch (index) {
				case "1":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell DESC");
					break;
				case "2":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countSell ASC");
					break;						
				case "3":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon DESC");
					break;
				case "4":
					sBuffer.append(" GROUP BY A.id ORDER BY E.countCon ASC");
					break;		
				case "5":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum DESC");
					break;
				case "6":
					sBuffer.append(" GROUP BY A.id ORDER BY H.conNum ASC");
					break;	
				case "7":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countPrice DESC");
					break;	
				case "8":
					sBuffer.append(" GROUP BY A.id ORDER BY D.countPrice ASC");
					break;	
			}	
		}
		return Db.paginate(pageNum, pageSize, "SELECT  A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(D.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ", sBuffer.toString(), params.toArray());
	}

	/**
	 * 普通商品 日、周、月销售统计
	 * @param pageNum
	 * @param pageSize
	 * @param goodId
	 * @param sellerId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param date
	 * @return
	 */
	public Page<Record> findXSByPage(int pageNum, int pageSize, int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,int date){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "inner JOIN t_bus_seller AS C ON B.sellerId= C.sellerId ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "LEFT JOIN (select J.goodsid ,sum(J.price) AS countPrice from t_bus_order_list J left join t_bus_order K on J.orderNum = K.orderNum  where K.orderState NOT in(1,5,7,9) group by J.goodsid) I ON I.goodsid = A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
		sBuffer.append(" GROUP BY A.id ORDER BY countSell desc, A.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 团购商品 日、周、月销售统计
	 * @param pageNum
	 * @param pageSize
	 * @param goodId
	 * @param sellerId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param date
	 * @return
	 */
	public Page<Record> findXSByPagegb(int pageNum, int pageSize, int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate,String sEndDate, String xBeginDate, String xEndDate, int state,int date){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore "
				+ "INNER JOIN t_dict_store AS B ON F.storeid = B.storeid "
				+ "INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order_gb where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ "LEFT JOIN (select K.goodsid ,sum(K.amount) AS countPrice from t_bus_order_gb K  where K.orderState NOT in(1,5,7,9) group by K.goodsid) I ON I.goodsid = A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if (StrKit.notBlank(sGoodName)) {
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.state=1");
			break;
		case 2:
			sBuffer.append(" AND A.state=2");
			break;
		case 3:
			sBuffer.append(" AND A.state=3");
			break;
		case 4:
			sBuffer.append(" AND A.state=4");
			break;
		}
		sBuffer.append(" AND A.groupbuying = 2 ");
		sBuffer.append(" GROUP BY A.id ORDER BY countSell desc, A.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(I.countPrice,0 ) as countPrice,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 查找分公司产品
	 */
	public GoodsStore findById(int id){
		return GoodsStore.me().findById(id);
	}
	/**
	 * 商品图片列表
	 * @param goodsid
	 * @return
	 */
	public List<GoodsBaseImages> findAllImages(String LunBoNum){
		return GoodsBaseImages.me().find("SELECT A.* FROM t_bus_goods_base_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	public List<DeatiImage> findDetailAllImages(String LunBoNum) {
		return DeatiImage.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	public GoodsStore findPageById(int id) {
		return GoodsStore.me().findFirst("SELECT A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(E.countCon,0) as countCon ,ifnull(H.conNum,0) as conNum "
				+ "FROM t_bus_goods AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore INNER JOIN t_dict_store AS B ON F.storeid = B.storeid INNER JOIN t_bus_seller AS C ON B.sellerId = C.sellerId "
				+ "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id "
				+ " left join t_bus_goods_look H on H.goodsid=A.id  where A.id=?",id);
	}
	public Record countNumber() {
		return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber,E.bhnumber FROM "
				+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where 1=1 and AA.groupbuying=1) A,"
				+ "(select COUNT(DISTINCT AA.id) as sjshnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=1 and AA.pstate=1 and AA.groupbuying=1) B,"
				+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=2 and AA.pstate=1 and AA.groupbuying=1) C,"
				+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=3 and AA.pstate=1 and AA.groupbuying=1) D,"
				+ "(select COUNT(DISTINCT AA.id) as bhnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=4 and AA.pstate=1 and AA.groupbuying=1) E");
	}
	public Record countNumbergb() {
		return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber,E.bhnumber FROM "
				+ "(select COUNT(DISTINCT AA.id) as tolnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where 1=1 and AA.groupbuying=2) A,"
				+ "(select COUNT(DISTINCT AA.id) as sjshnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=1 and AA.pstate=1 and AA.groupbuying=2) B,"
				+ "(select COUNT(DISTINCT AA.id) as sjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=2 and AA.pstate=1 and AA.groupbuying=2) C,"
				+ "(select COUNT(DISTINCT AA.id) as xjnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=3 and AA.pstate=1 and AA.groupbuying=2) D,"
				+ "(select COUNT(DISTINCT AA.id) as bhnumber FROM t_bus_goods AS AA INNER JOIN t_bus_goods_store_rel DD on AA.good_store=DD.goodstore INNER JOIN t_dict_store AS BB ON DD.storeid = BB.storeid inner JOIN t_bus_seller AS CC ON BB.sellerId= CC.sellerId where AA.state=4 and AA.pstate=1 and AA.groupbuying=2) E");
	}
	
	public boolean tooDown(GoodsStore goods) {
		return goods.update();
	}
	public Record findGoodDetailById(int id) {
		return Db.findFirst("select * from t_bus_goods  where id=?",id);
	}
	public boolean edit(GoodsStore goods) {
		return goods.update();
	}
	public List<Record> getGoodsItem(String gooditem) {
		return Db.find("select * from t_bus_goods_item where goodItem=?",gooditem);
	}
	public List<Record> getStore(String good_store) {
		return Db.find("SELECT A.title,A.address,A.tel FROM t_dict_store A WHERE A.storeid IN (select storeid from t_bus_goods_store_rel where goodstore=?) and A.qstate=1 and A.state=2 ",good_store);
	}
	
	/**
	 * 商品规格
	 * @param gooditem
	 * @return
	 */
	public List<Record> getGoodSpec(int goodsId) {
		return Db.find("select * from t_bus_goods_spec where goodId=?",goodsId);
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "商品统计列表", titles, listz);
	}
	public List<Record> findExcelByPage(int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate, String sEndDate,
			String xBeginDate, String xEndDate, int state, int date) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ,T.state as tState  FROM t_bus_goods AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore INNER JOIN t_dict_store AS B ON F.storeid = B.storeid inner JOIN t_bus_seller AS C ON B.sellerId= C.sellerId left join t_bus_goods_toker T on T.goodsId=A.id ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id"
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
			case 1:
				sBuffer.append(" AND A.pstate=1 and A.state=2");
				break;
			case 2:
				sBuffer.append(" AND A.pstate=1 and A.state=3");
				break;
			case 3:
				sBuffer.append(" AND A.pstate=2");
				break;
			case 4:
				sBuffer.append(" AND A.pstate=3");
				break;
		 
			default:
				//默认不查审核的商品
				sBuffer.append(" AND A.state!=1");
				break;
		}
		sBuffer.append(" AND A.groupbuying = 1 ");
		sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC, A.id DESC");
		return Db.find( sBuffer.toString(), params.toArray());
	}
	/**
	 * 导出团购商品
	 * @param goodId
	 * @param sGoodName
	 * @param sellerId
	 * @param storeid
	 * @param sBeginDate
	 * @param sEndDate
	 * @param xBeginDate
	 * @param xEndDate
	 * @param state
	 * @param date
	 * @return
	 */
	public List<Record> findExcelByPagegb(int goodId, String sGoodName, String sellerId, String storeid, String sBeginDate, String sEndDate,
			String xBeginDate, String xEndDate, int state, int date) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,B.title,C.sellerName,C.sellerId,ifnull(D.countSell,0 ) as countSell,ifnull(E.countCon,0) as countCon,ifnull(H.conNum,0) as conNum ,T.state as tState  FROM t_bus_goods AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store=F.goodstore INNER JOIN t_dict_store AS B ON F.storeid = B.storeid inner JOIN t_bus_seller AS C ON B.sellerId= C.sellerId left join t_bus_goods_toker T on T.goodsId=A.id ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) group by goodsid) D ON D.goodsid = A.id ");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') group by goodsid) D ON D.goodsid = A.id ");
			break;
		default:
			//默认全部
			sBuffer.append( "LEFT JOIN (select goodsid ,sum(countnum) AS countSell from t_bus_order where orderState NOT in(1,5,7,9) group by goodsid) D ON D.goodsid = A.id ");
			break;
		}
		sBuffer.append( "LEFT JOIN ( SELECT COUNT(id) as countCon,goodsid FROM t_bus_user_collection WHERE isCancel = 0 group by goodsid) E ON E.goodsid = A.id"
				+ " left join t_bus_goods_look H on H.goodsid=A.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(goodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(goodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(storeid)){
			sBuffer.append(" AND B.storeid in ('"+storeid+"')"+" AND C.sellerId in ('"+sellerId+"')");
		}else if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND A.uptime BETWEEN ? AND ? and A.state=2 and pstate=1");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(!StrKit.isBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')>=? and A.state=2 and pstate=1");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&!StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.uptime,'%Y-%m-%d')<=? and A.state=2 and pstate=1");
			params.add(sEndDate);
		}
		if(!StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND A.downtime BETWEEN ? AND ? and A.state=3 and pstate=1");
			params.add(xBeginDate);
			params.add(xEndDate);
		}else if(!StrKit.isBlank(xBeginDate)&&StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')>=? and A.state=3 and pstate=1");
			params.add(xBeginDate);
		}else if(StrKit.isBlank(xBeginDate)&&!StrKit.isBlank(xEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.downtime,'%Y-%m-%d')<=? and A.state=3 and pstate=1");
			params.add(xEndDate);
		}
		switch (state) {
			case 1:
				sBuffer.append(" AND A.pstate=1 and A.state=2");
				break;
			case 2:
				sBuffer.append(" AND A.pstate=1 and A.state=3");
				break;
			case 3:
				sBuffer.append(" AND A.pstate=2");
				break;
			case 4:
				sBuffer.append(" AND A.pstate=3");
				break;
		 
			default:
				//默认不查审核的商品
				sBuffer.append(" AND A.state!=1");
				break;
		}
		sBuffer.append(" AND A.groupbuying = 2 ");
		sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC, A.id DESC");
		return Db.find( sBuffer.toString(), params.toArray());
	}
	 /**
     * 根据商品id查询商品名称和积分
     * @param id
     * @return
     */
	public Record findGoodsById(int id) {
		return Db.findFirst("select g.name from t_bus_goods g left join t_bus_goods_sale s on s.goodsid=g.id  where g.id=?",id);
	}
    /***
     * 根据商品id查询收藏该商品的用户
     * @param pageNum
     * @param pageSize
     * @param id
     * @return
     */
	public Page<Record> findGoodsByCountCon( int pageNum, int pageSize,int id) {
		String sql="from t_bus_user_collection C "
				+ "left join t_bus_user_wx  WX on WX.id=C.userid "
				+ "left join t_bus_user U on U.id=C.userid "
				+ "left join (SELECT userid,count(id) id,SUM(amount) amount FROM t_bus_order GROUP BY userid) BD on BD.userid= WX.id	"
				+ "where C.goodsid="+id+"  AND  C.isCancel=0  group by WX.id ";

		return Db.paginate(pageNum, pageSize,
				"select U.*,count(BD.id) ordernum ,sum(BD.amount) amount ,U.storeName",sql);
	}
	 /***
	  * 数据导出
     * 根据商品id查询收藏该商品的用户
     * @param pageNum
     * @param pageSize
     * @param id
     * @return
     */
	public List<Record> findGoodsByCountConExcel(int id) {
		String sql="select U.*,count(BD.id) ordernum ,sum(BD.amount) amount "
				+ "from t_bus_user_collection C left join" 
				 +" t_bus_user_wx  WX on WX.id=C.userid left join "
				 +" t_bus_user U on U.id=WX.userid "
				 + "left join (SELECT userid,count(id) id,SUM(amount) amount FROM t_bus_order  GROUP BY userid) BD on BD.userid= WX.id"
				 +"	where C.goodsid="+id+"  AND  C.isCancel=0  group by WX.id ";
		return Db.find(sql);
	}
	/**
	 * 根据商品id查询所有的SKU
	 * @param id
	 * @return
	 */
	public List<GoodsSale> findAllSale(int id){
		return GoodsSale.me().find("SELECT * from  t_bus_goods_sale where goodsid=?",id);
	}
	/**
	 * 查询所有的一级category
	 * @param iid
	 * @return
	 */
	public List<Category> finOneCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=1");
	}
	/**
	 * 查询对应的二级category
	 * @param iid
	 * @return
	 */
	public List<Record> finTwoCategory(String categoryonid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categoryonid+"')");
	}
	/**
	 * 查询对应的三级category
	 * @param iid
	 * @return
	 */
	public List<Record> finThreeCategory(String categorytwid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorytwid+"')");
	}
	/**
	 * 查询对应的四级category
	 * @param iid
	 * @return
	 */
	public List<Record> finFourCategory(String categorythid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorythid+"')");
	}
	/**
	 * 查询所有的二级category
	 * @param iid
	 * @return
	 */
	public List<Category> findTwoCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=2");
	}
	/**
	 * 查询所有的三级category
	 * @param iid
	 * @return
	 */
	public List<Category> findThreeCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=3");
	}
	/**
	 * 查询所有的四级category
	 * @param iid
	 * @return
	 */
	public List<Category> findFourCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=4");
	}
	/**
	 * 查询所有的扩展信息
	 * @return
	 */
	public List<GoodsSaleItem> findSaleItem(int goodsid){
		return GoodsSaleItem.me().find("SELECT * FROM  t_bus_goods_sale_items where goodsid =?",goodsid);
	}
	/**
	 * 根据商品id查询当前商品的拓客信息
	 * @param id
	 * @return
	 */
	public Record findtoker(int id) {
		return Db.findFirst("select * from t_bus_goods_toker where goodsId=?",id);
	}
	public GoodsTokerValue findToker(int tokerid) {
		// TODO Auto-generated method stub
		return GoodsTokerValue.me().findFirst("SELECT * FROM t_bus_order_toker_user_log WHERE id=?",tokerid);
	}
	public User findUser(int id) {
		// TODO Auto-generated method stub
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE id=?",id);
	}
}